﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using SilverPOP.Data.Process.DAL;
using SilverPOP.Data.Process.Entity;

namespace SilverPOP.Data.Process.BLL
{
    public class SilverPOPRawReportBLL : BaseBLL
    {

        public static void InsertSilverPOPRawReportsDataBySQLScript(String sqlQueryString)
        {

            DAL.DAL.UpdateOrDeleteOrInsertDataUsingInLineQuery(sqlQueryString);
        }


        public static long GetSilverPOPDatabaseIdByCampaignID(long campaignId)
        {
            List<ParamWithValue> spParamValue = new List<ParamWithValue>();
            spParamValue.Add(new ParamWithValue("@campaignId", campaignId));
            List<DataReaderObject> readerObject = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Get_Active_Campaign_SilverPOP_DatabaseId", spParamValue);

            if (readerObject != null && readerObject.Count > 0)
            {
               return ((long) readerObject[0].ColumnValueList[0]);
            }
            else
            {
                return -1;
            }
        }


        #region Raw Report Queue Related Methods

        public static long InsertReportDataInReportQueueForProspectTracking(String fileName, long jobID, String jobStatus, long blastID, long mailingID, DateTime requestDate, String reportType,long campaignID,DateTime createdOn,long? contactlistId)
        {
            bool isExist = CheckActiveReportQueseDataExist(blastID, mailingID, reportType);

            if (isExist == false)
            {
               List<ParamWithValue> spParamValue = new List<ParamWithValue>();
               spParamValue.Add(new ParamWithValue("@FileName", fileName));
               spParamValue.Add(new ParamWithValue("@JobId", jobID));
               spParamValue.Add(new ParamWithValue("@JobStatus", jobStatus));
               spParamValue.Add(new ParamWithValue("@BlastId", blastID));
               spParamValue.Add(new ParamWithValue("@mailingId", mailingID));
                spParamValue.Add(new ParamWithValue("@contactListId", contactlistId == null ? 0 : contactlistId));
               spParamValue.Add(new ParamWithValue("@RequestDate", requestDate));
               spParamValue.Add(new ParamWithValue("@ReportType", reportType));
               spParamValue.Add(new ParamWithValue("@campaignId", campaignID));
               spParamValue.Add(new ParamWithValue("@CreatedOn", createdOn));

               List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Insert_Data_In_Raw_Reports_Job_Queue", spParamValue);

               if (dataList != null)
               {
                   return (long)dataList[0].ColumnValueList[0];
               }
               else
                   return 0;

              }

                return 0;
           
        }



        public static bool CheckActiveReportQueseDataExist(long blastID, long? mailingID, String reportType)
        {

            List<ParamWithValue> spParamValue = new List<ParamWithValue>();
            spParamValue.Add(new ParamWithValue("@BlastId", blastID));
            spParamValue.Add(new ParamWithValue("@mailingId", mailingID));
            spParamValue.Add(new ParamWithValue("@ReportType", reportType));

            List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Get_Active_Report_Queue_By_BlastId_And_MailingId", spParamValue);

            return dataList != null ? true : false;
        }


        public static List<ReportQueue> GetReportQueueByActiveBlastID(long blastID, String reportType)
        {

                List<ReportQueue> reportQueueList = new List<ReportQueue>();

                List<ParamWithValue> spParamValue = new List<ParamWithValue>();
                spParamValue.Add(new ParamWithValue("@BlastId", blastID));
                spParamValue.Add(new ParamWithValue("@ReportType", reportType));

                List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Get_Job_ID_For_Raw_Report", spParamValue);

                if (dataList != null)
                {
                    foreach (DataReaderObject dataReader in dataList)
                    {
                        ReportQueue queue = new ReportQueue();

                        queue.RawReportQueueId = (long) dataReader.ColumnValueList[0];
                        queue.CampaignId = (long)dataReader.ColumnValueList[1];
                        queue.ReportType = dataReader.ColumnValueList[2] != DBNull.Value ? dataReader.ColumnValueList[2].ToString() : String.Empty;
                        queue.BlastId = dataReader.ColumnValueList[3] != DBNull.Value ? (long) dataReader.ColumnValueList[3] : 0;
                        queue.MailingId = dataReader.ColumnValueList[4] != DBNull.Value ? (long) dataReader.ColumnValueList[4] : 0;
                        queue.ContactListId = dataReader.ColumnValueList[5] != DBNull.Value ? (long) dataReader.ColumnValueList[5] : 0;
                        queue.FileName = dataReader.ColumnValueList[6] != DBNull.Value ? dataReader.ColumnValueList[6].ToString() : String.Empty;
                        queue.JobId = dataReader.ColumnValueList[7] != DBNull.Value ? (long)dataReader.ColumnValueList[7] : 0;
                        queue.JobsStatus = dataReader.ColumnValueList[8] != DBNull.Value ? dataReader.ColumnValueList[8].ToString() : String.Empty;
                        queue.RequestDate = dataReader.ColumnValueList[9] != DBNull.Value ? (DateTime)dataReader.ColumnValueList[9] : (System.Nullable<DateTime>)null;
                        queue.IsFileParsed = dataReader.ColumnValueList[10] != DBNull.Value ? (bool) dataReader.ColumnValueList[10] : false;
                        queue.ParseDate = dataReader.ColumnValueList[11] != DBNull.Value ?(DateTime)dataReader.ColumnValueList[11] : (System.Nullable<DateTime>)null;
                        queue.IsActive = dataReader.ColumnValueList[12] != DBNull.Value ? (bool) dataReader.ColumnValueList[12] : false;

                        reportQueueList.Add(queue);

                    }

                    return (reportQueueList != null && reportQueueList.Count > 0) ? reportQueueList : null;
                    
                }
                return null;
        }

        public static List<long> GetActiveBlastIDFromReportQueue(String reportType)
        {

            List<long> blastIdList = new List<long>();
            
            List<ParamWithValue> spParamValue = new List<ParamWithValue>();
            spParamValue.Add(new ParamWithValue("@ReportType", reportType));

            List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Get_Active_Blast_Id_From_Report_Queue", spParamValue);

            if (dataList != null)
            {
                foreach (DataReaderObject dataReader in dataList)
                {
                    if ((long)dataReader.ColumnValueList[0] > 0)
                    {
                        blastIdList.Add((long)dataReader.ColumnValueList[0]);
                    }
                }

                return (blastIdList != null && blastIdList.Count > 0) ? blastIdList : null;
            }

            return null;
        }


        public static int UpdateReportQueueData(long queueId, String jobStatus, DateTime parseDate)
        {
            List<ParamWithValue> spParamValue = new List<ParamWithValue>();
            spParamValue.Add(new ParamWithValue("@RawReportQueueId", queueId));
            spParamValue.Add(new ParamWithValue("@JobStatus", jobStatus));
            spParamValue.Add(new ParamWithValue("@ParseDate", parseDate));

            List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Update_RawReportsJobQueue", spParamValue);

            if (dataList != null)
            {
            
                return (int) dataList[0].ColumnValueList[0];
            }

            return 0;

        }

        #endregion


        #region Get Active Mailing IDs
        public static List<ReportQueue> GetAllActiveMailingIdByBlasId(long blastId)
        {
            List<ParamWithValue> spParamValue = new List<ParamWithValue>();
            spParamValue.Add(new ParamWithValue("@blastId", blastId));

            List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Get_Active_MailingId_By_BlastId",spParamValue);

            if (dataList != null)
            {

                List<ReportQueue> mailingIdList = new List<ReportQueue>();

                foreach (DataReaderObject dataReader in dataList)
                {
                   ReportQueue reportQueue = new ReportQueue();

                   if ((long)dataReader.ColumnValueList[0] > 0 && (long) dataReader.ColumnValueList[1] > 0)
                   {
                        
                        

                       reportQueue.MailingId = (long) dataReader.ColumnValueList[0];
                        reportQueue.BlastId = (long)dataReader.ColumnValueList[1];
                        reportQueue.ContactListId = (dataReader.ColumnValueList[2] != System.DBNull.Value && dataReader.ColumnValueList[2] != null) 
                            ? (long)dataReader.ColumnValueList[2] : new Nullable<long>();
                            
                        mailingIdList.Add(reportQueue);
                   }
               }

                return (mailingIdList != null && mailingIdList.Count > 0) ? mailingIdList : null;
            }

            return null;
        }
        #endregion

        public static bool IsSentEventInsertedInDatabase(long campaignId, long schedulerMailingId)
        {
            List<ParamWithValue> spParamValue = new List<ParamWithValue>();
            spParamValue.Add(new ParamWithValue("@campaignId", campaignId));
            spParamValue.Add(new ParamWithValue("@MailingId", schedulerMailingId));

            List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Sent_Event_Type_Count_Check", spParamValue);

            if (dataList != null && dataList[0].ColumnValueList != null && dataList[0].ColumnValueList[0] != DBNull.Value)
            {
                int count = (int)dataList[0].ColumnValueList[0];

                if (count > 0)
                    return true;
                else
                    return false;
            }

            return false;
        }

        public static long GetSegmentIDByBlastIDAndSegmentID(long blastId,long schedulerMailingId)
        {
            List<ParamWithValue> spParamValue = new List<ParamWithValue>();
            spParamValue.Add(new ParamWithValue("@BlastId", blastId));
            spParamValue.Add(new ParamWithValue("@SchedulerMailingId", schedulerMailingId));

            List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Get_SegmentId_By_Blast_And_SchedulerMailing_Id", spParamValue);

            if (dataList != null)
            {
                return dataList[0].ColumnValueList != null ? (long)dataList[0].ColumnValueList[0] : 0;
            }

            return 0;
        }


        public static void InsertSilverPOPEventsCount(long blastID, long SchedulerMailingId,long segmentId,DateTime dateCreated)
        {
            List<ParamWithValue> spParamValue = new List<ParamWithValue>();
            spParamValue.Add(new ParamWithValue("@BlastId", blastID));
            spParamValue.Add(new ParamWithValue("@SchedulerMailingId", SchedulerMailingId));
            spParamValue.Add(new ParamWithValue("@SegmentId", segmentId));
            spParamValue.Add(new ParamWithValue("@DateCreated", dateCreated));


            List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureWithParametersAndGetDataObject("SPR_Insert_SilverPOP_Analytics_Events_Counts", spParamValue);

        }


        public static void UpdateOptOutOrBouncedIndicatorInDatabase(DateTime currentDate)
        {
            List<DataReaderObject> dataList = DAL.DAL.ExecuteStoredProcedureAndGetDataObject("SPR_Update_OptOut_Bounce_Indicator");
        }


    }//end class
}
